package com.jetlion.sqlite.db;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.jetlion.sqlite.entity.Student;

import java.util.ArrayList;
import java.util.List;

public class DBManager {

    private SQLiteDatabase sqLiteDatabase;

    public DBManager(Context context) {
        DBHelper dbHelp = new DBHelper(context);
        //获得数据库
        sqLiteDatabase = dbHelp.getWritableDatabase();
    }

    /**
     * 关闭数据库
     */
    public void finish() {
        if (null != sqLiteDatabase) {
            sqLiteDatabase.close();
            sqLiteDatabase = null;
        }

    }

    /**
     * 添加学生
     *
     * @param students
     */
    public void add(List<Student> students) {

        //开启事务
        sqLiteDatabase.beginTransaction();

        for (Student student : students) {
            ContentValues values = new ContentValues();
            values.put("stuNo", student.getStuNo());
            values.put("stuName", student.getStuName());
            values.put("stuSex", student.getStuSex());
            values.put("stuScore", student.getStuScore());

            /**
             * nullColumnHack
             * 当values参数为空或者里面没有内容的时候，insert是会失败的(底层数据库不允许插入一个空行)，
             * 为了防止这种情况，要在这里指定一个列名，到时候如果发现将要插入的行为空行时，就会将你指定的这个列名的值设为null，
             * 然后再向数据库中插入。
             *
             * 若不添加nullColumnHack则sql语句最终的结果将会类似insert into tableName()values();
             * 这是不允许的。
             * 若添加上nullColumnHack则sql语句将会变成insert into tableName (nullColumnHack)values(null);
             * 这是可以的。
             * */
            //执行插入语句
            sqLiteDatabase.insert("score", null, values);
        }

        //设置事务成功
        sqLiteDatabase.setTransactionSuccessful();

        //结束事务
        sqLiteDatabase.endTransaction();
    }

    /**
     * 删除
     *
     * @param stuNo
     * @return
     */
    public int delete(String stuNo) {
        return sqLiteDatabase.delete("score", "stuNo=?", new String[]{stuNo});
    }

    /**
     * 修改
     *
     * @param stuNo
     * @param score
     * @return
     */
    public int update(String stuNo, Integer score) {

        //语句的方式修改
        //String sql = "update score set stuScore=" + score + "where stuNo=" + stuNo;
        //sqLiteDatabase.execSQL(sql);

        //方法的方式修改
        ContentValues values = new ContentValues();
        //修改的列和值
        values.put("stuScore", score);
        return sqLiteDatabase.update("score", values, "stuNo=?", new String[]{stuNo});
    }


    /**
     * 查询
     *
     * @return
     */
    @SuppressLint("Range")
    public List<Student> query() {

        List<Student> list = new ArrayList<>();

        //查询
        Cursor cursor = sqLiteDatabase.query("score", null, null, null, null, null, null, null);

        while (cursor.moveToNext()) {
            Student student = new Student();
            student.setStuNo(cursor.getString(cursor.getColumnIndex("stuNo")));
            student.setStuName(cursor.getString(cursor.getColumnIndex("stuName")));
            student.setStuSex(cursor.getString(cursor.getColumnIndex("stuSex")));
            student.setStuScore(cursor.getInt(cursor.getColumnIndex("stuScore")));
            list.add(student);
        }
        return list;
    }

}
